﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;
using System.Text.RegularExpressions;
using System.Xml;

namespace Labthink.PCSoft.UniversalHost4.Utility
{
    public class SqliteHelper
    {
        private SqliteHelper()
        {
        }

        public static string CreateConnection(string filename)
        {
            return $"Data Source={filename};Version=3";
        }

        public static string CreateConnectionString(string filename)
        {
            return $"Data Source={filename};Version=3";
        }

        /// <summary>
        /// 创建命令.
        /// </summary>
        /// <param name="connection">数据库连接.</param>
        /// <param name="commandText">命令语句</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>命令</returns>
        public static SQLiteCommand CreateCommand(SQLiteConnection connection, string commandText, params SQLiteParameter[] commandParameters)
        {
            var cmd = new SQLiteCommand(commandText, connection);
            if (commandParameters.Length > 0)
            {
                foreach (SQLiteParameter param in commandParameters)
                    cmd.Parameters.Add(param);
            }
            return cmd;
        }

        /// <summary>
        /// 创建命令
        /// </summary>
        /// <param name="connectionString">链接</param>
        /// <param name="commandText">语句.</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>命令</returns>
        public static SQLiteCommand CreateCommand(string connectionString, string commandText, params SQLiteParameter[] commandParameters)
        {
            if (!connectionString.StartsWith("Data Source"))
            {
                connectionString = CreateConnection(connectionString);
            }
            var cn = new SQLiteConnection(connectionString);

            var cmd = new SQLiteCommand(commandText, cn);

            if (commandParameters.Length > 0)
            {
                foreach (SQLiteParameter param in commandParameters)
                    cmd.Parameters.Add(param);
            }
            return cmd;
        }

        /// <summary>
        /// 创建参数.
        /// </summary>
        /// <param name="parameterName">参数名</param>
        /// <param name="parameterType">参数类型.</param>
        /// <param name="parameterValue">参数值.</param>
        /// <returns>参数</returns>
        public static SQLiteParameter CreateParameter(string parameterName, DbType parameterType, object parameterValue)
        {
            var parameter = new SQLiteParameter
            {
                DbType = parameterType,
                ParameterName = parameterName,
                Value = parameterValue
            };
            return parameter;
        }

        /// <summary>
        /// 快捷的方法来执行数据集从SQL语句和object[]array参数值
        /// </summary>
        /// <param name="connectionString">SQLite连接字符串</param>
        /// <param name="commandText">使用嵌入式SQL语句参数名称“@param”风格</param>
        /// <param name="paramList">object[]数组的参数值</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string connectionString, string commandText, object[] paramList)
        {
            if (!connectionString.StartsWith("Data Source"))
            {
                connectionString = CreateConnection(connectionString);
            }
            var cn = new SQLiteConnection(connectionString);
            var cmd = cn.CreateCommand();

            cmd.CommandText = commandText;
            if (paramList != null)
            {
                AttachParameters(cmd, commandText, paramList);
            }
            var ds = new DataSet();
            if (cn.State == ConnectionState.Broken)
                cn.Close();

            if (cn.State == ConnectionState.Closed)
                cn.Open();
            var da = new SQLiteDataAdapter(cmd);
            da.Fill(ds);
            da.Dispose();
            cmd.Dispose();
            cn.Close();
            return ds;
        }

        /// <summary>
        /// 快捷的方法来执行数据集从SQL语句和object[]array参数值
        /// </summary>
        /// <param name="cn">连接.</param>
        /// <param name="commandText">命令文本.</param>
        /// <param name="paramList">参数列表.</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(SQLiteConnection cn, string commandText, object[] paramList)
        {
            SQLiteCommand cmd = cn.CreateCommand();

            cmd.CommandText = commandText;
            if (paramList != null)
            {
                AttachParameters(cmd, commandText, paramList);
            }
            var ds = new DataSet();
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            var da = new SQLiteDataAdapter(cmd);
            da.Fill(ds);
            da.Dispose();
            cmd.Dispose();
            cn.Close();
            return ds;
        }

        /// <summary>
        /// 执行数据集从稠密的命令对象.
        /// </summary>
        /// <param name="cmd">完全填充SQLiteCommand</param>
        /// <returns>数据集</returns>
        public static DataSet ExecuteDataSet(SQLiteCommand cmd)
        {
            if (cmd.Connection.State == ConnectionState.Closed)
                cmd.Connection.Open();
            var ds = new DataSet();
            var da = new SQLiteDataAdapter(cmd);
            da.Fill(ds);
            da.Dispose();
            cmd.Connection.Close();
            cmd.Dispose();
            return ds;
        }

        /// <summary>
        /// 执行数据集在一个SQLite事务
        /// </summary>
        /// <param name="transaction">SQLiteTransaction。事务由连接、事务和命令, 所有这些必须创建并在这个方法调用. </param>
        /// <param name="commandText">命令文本.</param>
        /// <param name="commandParameters">Sqlite命令参数.</param>
        /// <returns>数据集</returns>
        /// <remarks>用户必须检查事务对象和处理事务。连接。关闭等.</remarks>
        public static DataSet ExecuteDataSet(SQLiteTransaction transaction, string commandText, params SQLiteParameter[] commandParameters)
        {
            if (transaction == null) throw new ArgumentNullException(nameof(transaction));
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed, please provide an open transaction.", nameof(transaction));
            IDbCommand cmd = transaction.Connection.CreateCommand();
            cmd.CommandText = commandText;
            foreach (SQLiteParameter param in commandParameters)
            {
                cmd.Parameters.Add(param);
            }

            if (transaction.Connection.State == ConnectionState.Closed)
                transaction.Connection.Open();
            DataSet ds = ExecuteDataSet((SQLiteCommand)cmd);
            return ds;
        }

        /// <summary>
        /// 执行数据集在一个SQLite事务.
        /// </summary>
        /// <param name="transaction">SQLiteTransaction。事务由连接、事务和命令, 所有这些必须创建并在这个方法调用. </param>
        /// <param name="commandText">命令文本.</param>
        /// <param name="commandParameters">object[]数组的参数值.</param>
        /// <returns>数据集</returns>
        /// <remarks>用户必须检查事务对象和处理事务。连接。关闭等.</remarks>
        public static DataSet ExecuteDataSet(SQLiteTransaction transaction, string commandText, object[] commandParameters)
        {
            if (transaction == null) throw new ArgumentNullException(nameof(transaction));
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed,                                                          please provide an open transaction.", nameof(transaction));
            IDbCommand cmd = transaction.Connection.CreateCommand();
            cmd.CommandText = commandText;
            AttachParameters((SQLiteCommand)cmd, cmd.CommandText, commandParameters);
            if (transaction.Connection.State == ConnectionState.Closed)
                transaction.Connection.Open();

            DataSet ds = ExecuteDataSet((SQLiteCommand)cmd);
            return ds;
        }

        /// <summary>
        /// 执行相应的命令对于每个插入、更新或删除行数据集
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order");
        /// </remarks>
        /// <param name="insertCommand">A valid SQL statement  to insert new records into the data source</param>
        /// <param name="deleteCommand">A valid SQL statement to delete records from the data source</param>
        /// <param name="updateCommand">A valid SQL statement used to update records in the data source</param>
        /// <param name="dataSet">The DataSet used to update the data source</param>
        /// <param name="tableName">The DataTable used to update the data source.</param>
        public static void UpdateDataset(SQLiteCommand insertCommand, SQLiteCommand deleteCommand, SQLiteCommand updateCommand, DataSet dataSet, string tableName)
        {
            if (insertCommand == null) throw new ArgumentNullException(nameof(insertCommand));
            if (deleteCommand == null) throw new ArgumentNullException(nameof(deleteCommand));
            if (updateCommand == null) throw new ArgumentNullException(nameof(updateCommand));
            if (string.IsNullOrEmpty(tableName)) throw new ArgumentNullException(nameof(tableName));

            // Create a SQLiteDataAdapter, and dispose of it after we are done
            using (var dataAdapter = new SQLiteDataAdapter())
            {
                // Set the data adapter commands
                dataAdapter.UpdateCommand = updateCommand;
                dataAdapter.InsertCommand = insertCommand;
                dataAdapter.DeleteCommand = deleteCommand;

                // Update the dataset changes in the data source
                dataAdapter.Update(dataSet, tableName);

                // Commit all the changes made to the DataSet
                dataSet.AcceptChanges();
            }
        }

        public static void UpdateDataset(SQLiteCommand insertCommand, SQLiteCommand deleteCommand, SQLiteCommand updateCommand, DataTable dataTable)
        {
            if (insertCommand == null) throw new ArgumentNullException(nameof(insertCommand));
            if (deleteCommand == null) throw new ArgumentNullException(nameof(deleteCommand));
            if (updateCommand == null) throw new ArgumentNullException(nameof(updateCommand));
            //if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");

            // Create a SQLiteDataAdapter, and dispose of it after we are done
            using (var dataAdapter = new SQLiteDataAdapter())
            {
                // Set the data adapter commands
                dataAdapter.UpdateCommand = updateCommand;
                dataAdapter.InsertCommand = insertCommand;
                dataAdapter.DeleteCommand = deleteCommand;

                // Update the dataset changes in the data source
                dataAdapter.Update(dataTable);

                // Commit all the changes made to the DataSet
                dataTable.AcceptChanges();
            }
        }

        public static void UpdateDataset(SQLiteCommand insertCommand, DataSet dataSet, string tableName)
        {
            if (insertCommand == null) throw new ArgumentNullException(nameof(insertCommand));
            //if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
            //if (updateCommand == null) throw new ArgumentNullException("updateCommand");
            if (string.IsNullOrEmpty(tableName)) throw new ArgumentNullException(nameof(tableName));

            // Create a SQLiteDataAdapter, and dispose of it after we are done
            using (var dataAdapter = new SQLiteDataAdapter())
            {
                // Set the data adapter commands
                //dataAdapter.UpdateCommand = updateCommand;
                dataAdapter.InsertCommand = insertCommand;
                //dataAdapter.DeleteCommand = deleteCommand;

                // Update the dataset changes in the data source
                dataAdapter.Update(dataSet, tableName);

                // Commit all the changes made to the DataSet
                dataSet.AcceptChanges();
            }
        }

        public static void UpdateDataSet(SQLiteCommand insertCommand, DataTable dataTable)
        {
            if (insertCommand == null) throw new ArgumentNullException("insertCommand");
            //if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
            //if (updateCommand == null) throw new ArgumentNullException("updateCommand");
            //if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");

            // Create a SQLiteDataAdapter, and dispose of it after we are done
            using (var dataAdapter = new SQLiteDataAdapter())
            {
                // Set the data adapter commands
                //dataAdapter.UpdateCommand = updateCommand;
                dataAdapter.InsertCommand = insertCommand;
                //dataAdapter.DeleteCommand = deleteCommand;

                // Update the dataset changes in the data source
                dataAdapter.Update(dataTable);

                // Commit all the changes made to the DataSet
                dataTable.AcceptChanges();
            }
        }

        public static void UpdateDataset(SQLiteCommand insertCommand, DataTable dataTable)
        {
            if (insertCommand == null) throw new ArgumentNullException(nameof(insertCommand));
            //if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
            //if (updateCommand == null) throw new ArgumentNullException("updateCommand");
            //if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");

            // Create a SQLiteDataAdapter, and dispose of it after we are done
            using (var dataAdapter = new SQLiteDataAdapter())
            {
                // Set the data adapter commands
                //dataAdapter.UpdateCommand = updateCommand;
                dataAdapter.InsertCommand = insertCommand;
                //dataAdapter.DeleteCommand = deleteCommand;

                // Update the dataset changes in the data source
                dataAdapter.Update(dataTable);

                // Commit all the changes made to the DataSet
                dataTable.AcceptChanges();
            }
        }

        public static void UpdateDataset(DbCommand insertCommand, DataTable dataTable)
        {
            if (insertCommand == null) throw new ArgumentNullException(nameof(insertCommand));
            //if (deleteCommand == null) throw new ArgumentNullException("deleteCommand");
            //if (updateCommand == null) throw new ArgumentNullException("updateCommand");
            //if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName");

            // Create a SQLiteDataAdapter, and dispose of it after we are done
            using (DbDataAdapter dataAdapter = new SQLiteDataAdapter())
            {
                // Set the data adapter commands
                //dataAdapter.UpdateCommand = updateCommand;
                dataAdapter.InsertCommand = insertCommand;
                //dataAdapter.DeleteCommand = deleteCommand;

                // Update the dataset changes in the data source
                dataAdapter.Update(dataTable);

                // Commit all the changes made to the DataSet
                dataTable.AcceptChanges();
            }
        }

        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="recordCount"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="cmdText"></param>
        /// <param name="countText"></param>
        /// <param name="p"></param>
        /// <returns></returns>
        public static DataSet ExecutePager(string connectionString, ref int recordCount, int pageIndex, int pageSize, string cmdText, string countText, params object[] p)
        {
            if (!connectionString.StartsWith("Data Source"))
            {
                connectionString = CreateConnection(connectionString);
            }

            if (recordCount < 0)
                recordCount = int.Parse(ExecuteScalar(connectionString, countText, p).ToString());
            var cn = new SQLiteConnection(connectionString);
            var cmd = cn.CreateCommand();
            cmd.CommandText = cmdText;
            AttachParameters(cmd, cmdText, p);
            var ds = new DataSet();
            if (cn.State == ConnectionState.Closed)
                cn.Open();

            var da = new SQLiteDataAdapter(cmd);
            da.Fill(ds, (pageIndex - 1) * pageSize, pageSize, "result");
            da.Dispose();
            cmd.Dispose();
            cn.Close();
            return ds;
        }

        /// <summary>
        /// Shortcut method to return IDataReader
        /// NOTE: You should explicitly close the Command.connection you passed in as
        /// well as call Dispose on the Command  after reader is closed.
        /// We do this because IDataReader has no underlying Connection Property.
        /// </summary>
        /// <param name="cmd">SQLiteCommand Object</param>
        /// <param name="commandText">SQL Statement with optional embedded "@param" style parameters</param>
        /// <param name="paramList">object[] array of parameter values</param>
        /// <returns>IDataReader</returns>
        public static IDataReader ExecuteReader(SQLiteCommand cmd, string commandText, object[] paramList)
        {
            if (cmd.Connection == null)
                throw new ArgumentException("Command must have live connection attached.", nameof(cmd));
            cmd.CommandText = commandText;
            AttachParameters(cmd, commandText, paramList);
            if (cmd.Connection.State == ConnectionState.Closed)
                cmd.Connection.Open();
            IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return rdr;
        }

        /// <summary>
        /// Shortcut to ExecuteNonQuery with SqlStatement and object[] param values
        /// </summary>
        /// <param name="connectionString">SQLite Connection String</param>
        /// <param name="commandText">Sql Statement with embedded "@param" style parameters</param>
        /// <param name="paramList">object[] array of parameter values</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connectionString, string commandText, params object[] paramList)
        {
            if (!connectionString.StartsWith("Data Source"))
            {
                connectionString = CreateConnection(connectionString);
            }

            var cn = new SQLiteConnection(connectionString);
            var cmd = cn.CreateCommand();
            cmd.CommandText = commandText;
            AttachParameters(cmd, commandText, paramList);
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            int result;
            try
            {
                result = cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Dispose();
                cn.Close();
            }


            return result;
        }

        public static int ExecuteNonQuery(SQLiteConnection cn, string commandText, params object[] paramList)
        {
            SQLiteCommand cmd = cn.CreateCommand();
            cmd.CommandText = commandText;
            AttachParameters(cmd, commandText, paramList);
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            int result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            cn.Close();

            return result;
        }

        /// <summary>
        /// Executes  non-query sql Statement with Transaction
        /// </summary>
        /// <param name="transaction">SQLiteTransaction. Transaction consists of Connection, Transaction, and Command, all of which must be created prior to making this method call. </param>
        /// <param name="commandText">Command text.</param>
        /// <param name="paramList">Param list.</param>
        /// <returns>Integer</returns>
        /// <remarks>user must examine Transaction Object and handle transaction.connection .Close, etc.</remarks>
        public static int ExecuteNonQuery(SQLiteTransaction transaction, string commandText, params object[] paramList)
        {
            if (transaction == null) throw new ArgumentNullException(nameof(transaction));
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed,                                                        please provide an open transaction.", nameof(transaction));
            IDbCommand cmd = transaction.Connection.CreateCommand();
            cmd.CommandText = commandText;
            AttachParameters((SQLiteCommand)cmd, cmd.CommandText, paramList);
            if (transaction.Connection.State == ConnectionState.Closed)
                transaction.Connection.Open();
            int result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return result;
        }

        public static int ExecuteNonQuery(DbTransaction transaction, string commandText, params object[] paramList)
        {
            if (transaction == null) throw new ArgumentNullException(nameof(transaction));
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed,                                                        please provide an open transaction.", nameof(transaction));
            IDbCommand cmd = transaction.Connection.CreateCommand();
            cmd.CommandText = commandText;
            AttachParameters((SQLiteCommand)cmd, cmd.CommandText, paramList);
            if (transaction.Connection.State == ConnectionState.Closed)
                transaction.Connection.Open();
            int result = cmd.ExecuteNonQuery();
            cmd.Dispose();
            return result;
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="cmd">CMD.</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(IDbCommand cmd)
        {
            if (cmd.Connection.State == ConnectionState.Closed)
                cmd.Connection.Open();
            int result = cmd.ExecuteNonQuery();
            cmd.Connection.Close();
            cmd.Dispose();
            return result;
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="cmd">CMD.</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(DbTransaction transaction, IDbCommand cmd)
        {
            cmd.Transaction = transaction;
            int result = cmd.ExecuteNonQuery();
            //cmd.Connection.Close();
            //cmd.Dispose();
            cmd.Dispose();
            return result;
        }

        /// <summary>
        /// Shortcut to ExecuteScalar with Sql Statement embedded params and object[] param values
        /// </summary>
        /// <param name="connectionString">SQLite Connection String</param>
        /// <param name="commandText">SQL statement with embedded "@param" style parameters</param>
        /// <param name="paramList">object[] array of param values</param>
        /// <returns></returns>
        public static object ExecuteScalar(string connectionString, string commandText, params object[] paramList)
        {
            if (!connectionString.StartsWith("Data Source"))
            {
                connectionString = CreateConnection(connectionString);
            }
            var cn = new SQLiteConnection(connectionString);
            var cmd = cn.CreateCommand();
            cmd.CommandText = commandText;
            AttachParameters(cmd, commandText, paramList);
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            object result = cmd.ExecuteScalar();
            cmd.Dispose();
            cn.Close();

            return result;
        }

        public static object ExecuteScalar(SQLiteTransaction transaction, string commandText, params  object[] paramList)
        {
            if (transaction == null) throw new ArgumentNullException("transaction");
            if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed,                                                        please provide an open transaction.", "transaction");
            IDbCommand cmd = transaction.Connection.CreateCommand();
            cmd.CommandText = commandText;
            AttachParameters((SQLiteCommand)cmd, commandText, paramList);
            if (transaction.Connection.State == ConnectionState.Closed)
                transaction.Connection.Open();
            object result = cmd.ExecuteScalar();
            cmd.Dispose();

            return result;
        }

        public static object ExecuteScalarLength(string connectionString, string commandText, object[] paramList)
        {
            if (!connectionString.StartsWith("Data Source"))
            {
                connectionString = CreateConnection(connectionString);
            }
            var cn = new SQLiteConnection(connectionString);
            var cmd = cn.CreateCommand();
            cmd.CommandText = commandText;
            AttachParameters(cmd, commandText, paramList);
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            object result = cmd.ExecuteScalar();
            cmd.Dispose();
            cn.Close();

            return result;
        }

        /// <summary>
        /// Execute XmlReader with complete Command
        /// </summary>
        /// <param name="command">SQLite Command</param>
        /// <returns>XmlReader</returns>
        public static XmlReader ExecuteXmlReader(IDbCommand command)
        { 
            // open the connection if necessary, but make sure we 
            // know to close it when were done.
            if (command.Connection.State != ConnectionState.Open)
            {
                command.Connection.Open();
            }

            // get a data adapter  
            var da = new SQLiteDataAdapter((SQLiteCommand)command);
            var ds = new DataSet();
            // fill the data set, and return the schema information
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            da.Fill(ds);
            // convert our dataset to XML
            var stream = new StringReader(ds.GetXml());
            command.Connection.Close();
            // convert our stream of text to an XmlReader
            return new XmlTextReader(stream);
        }


        /// <summary>
        /// Parses parameter names from SQL Statement, assigns values from object array, and returns fully populated ParameterCollection.
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="commandText">Sql Statement with "@param" style embedded parameters</param>
        /// <param name="paramList">object[] array of parameter values</param>
        /// <returns>SQLiteParameterCollection</returns>
        /// <remarks>Status experimental. Regex appears to be handling most issues. Note that parameter object array must be in same ///order as parameter names appear in SQL statement.</remarks>
        private static void AttachParameters(SQLiteCommand cmd, string commandText, params object[] paramList)
        {
            if (paramList == null || paramList.Length == 0) return;

            SQLiteParameterCollection coll = cmd.Parameters;
            // ReSharper disable once StringIndexOfIsCultureSpecific.1
            string paramString = commandText.Substring(commandText.IndexOf("@"));
            // pre-process the string so always at least 1 space after a comma.
            paramString = paramString.Replace(",", " ,");
            // get the named parameters into a match collection
            const string pattern = @"(@)\S*(.*?)\b";
            var ex = new Regex(pattern, RegexOptions.IgnoreCase);
            var mc = ex.Matches(paramString);
            var paramNames = new string[mc.Count];
            int i = 0;
            foreach (Match m in mc)
            {
                paramNames[i] = m.Value;
                i++;
            }

            // now let's type the parameters
            var j = 0;
            foreach (object o in paramList)
            {
                Type t = o.GetType();

                var param = new SQLiteParameter();
                switch (t.ToString())
                {
                    case "DBNull":
                    case "Char":
                    case "SByte":
                    case "UInt16":
                    case "UInt32":
                    case "UInt64":
                        throw new SystemException("Invalid data type");


                    case "System.String":
                        param.DbType = DbType.String;
                        param.ParameterName = paramNames[j];
                        param.Value = paramList[j];
                        coll.Add(param);
                        break;

                    case "System.Byte[]":
                        param.DbType = DbType.Binary;
                        param.ParameterName = paramNames[j];
                        param.Value = paramList[j];
                        coll.Add(param);
                        break;

                    case "System.Int32":
                        param.DbType = DbType.Int32;
                        param.ParameterName = paramNames[j];
                        param.Value = (int)paramList[j];
                        coll.Add(param);
                        break;

                    case "System.Int64":
                        param.DbType = DbType.Int64;
                        param.ParameterName = paramNames[j];
                        param.Value = (long)paramList[j];
                        coll.Add(param);
                        break;

                    case "System.Boolean":
                        param.DbType = DbType.Boolean;
                        param.ParameterName = paramNames[j];
                        param.Value = (bool)paramList[j];
                        coll.Add(param);
                        break;

                    case "System.DateTime":
                        param.DbType = DbType.DateTime;
                        param.ParameterName = paramNames[j];
                        param.Value = Convert.ToDateTime(paramList[j]);
                        coll.Add(param);
                        break;

                    case "System.Double":
                        param.DbType = DbType.Double;
                        param.ParameterName = paramNames[j];
                        param.Value = Convert.ToDouble(paramList[j]);
                        coll.Add(param);
                        break;

                    case "System.Decimal":
                        param.DbType = DbType.Decimal;
                        param.ParameterName = paramNames[j];
                        param.Value = Convert.ToDecimal(paramList[j]);
                        break;

                    case "System.Guid":
                        param.DbType = DbType.Guid;
                        param.ParameterName = paramNames[j];
                        param.Value = (Guid)paramList[j];
                        break;

                    case "System.Object":

                        param.DbType = DbType.Object;
                        param.ParameterName = paramNames[j];
                        param.Value = paramList[j];
                        coll.Add(param);
                        break;

                    default:
                        throw new SystemException("Value is of unknown data type");

                } // end switch

                j++;
            }
        }

        /// <summary>
        /// Executes non query typed params from a DataRow
        /// </summary>
        /// <param name="command">Command.</param>
        /// <param name="dataRow">Data row.</param>
        /// <returns>Integer result code</returns>
        public static int ExecuteNonQueryTypedParams(IDbCommand command, DataRow dataRow)
        {
            int retVal;

            // If the row has values, the store procedure parameters must be initialized
            if (dataRow != null && dataRow.ItemArray.Length > 0)
            {
                // Set the parameters values
                AssignParameterValues(command.Parameters, dataRow);

                retVal = ExecuteNonQuery(command);
            }
            else
            {
                retVal = ExecuteNonQuery(command);
            }

            return retVal;
        }

        /// <summary>
        /// This method assigns dataRow column values to an IDataParameterCollection
        /// </summary>
        /// <param name="commandParameters">The IDataParameterCollection to be assigned values</param>
        /// <param name="dataRow">The dataRow used to hold the command's parameter values</param>
        /// <exception cref="InvalidOperationException">Thrown if any of the parameter names are invalid.</exception>
        protected internal static void AssignParameterValues(IDataParameterCollection commandParameters, DataRow dataRow)
        {
            if (commandParameters == null || dataRow == null)
            {
                // Do nothing if we get no data
                return;
            }

            DataColumnCollection columns = dataRow.Table.Columns;

            int i = 0;
            // Set the parameters values
            foreach (IDataParameter commandParameter in commandParameters)
            {
                // Check the parameter name
                if (commandParameter.ParameterName == null ||
                 commandParameter.ParameterName.Length <= 1)
                    throw new InvalidOperationException(
                        $"Please provide a valid parameter name on the parameter #{i},the ParameterName property has the following value: '{commandParameter.ParameterName}'.");

                if (columns.Contains(commandParameter.ParameterName))
                    commandParameter.Value = dataRow[commandParameter.ParameterName];
                else if (columns.Contains(commandParameter.ParameterName.Substring(1)))
                    commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];

                i++;
            }
        }

        /// <summary>
        /// This method assigns dataRow column values to an array of IDataParameters
        /// </summary>
        /// <param name="commandParameters">Array of IDataParameters to be assigned values</param>
        /// <param name="dataRow">The dataRow used to hold the stored procedure's parameter values</param>
        /// <exception cref="InvalidOperationException">Thrown if any of the parameter names are invalid.</exception>
        protected void AssignParameterValues(IDataParameter[] commandParameters, DataRow dataRow)
        {
            if (commandParameters == null || dataRow == null)
            {
                // Do nothing if we get no data
                return;
            }

            DataColumnCollection columns = dataRow.Table.Columns;

            int i = 0;
            // Set the parameters values
            foreach (IDataParameter commandParameter in commandParameters)
            {
                // Check the parameter name
                if (commandParameter.ParameterName == null ||
                 commandParameter.ParameterName.Length <= 1)
                    throw new InvalidOperationException(
                        $"Please provide a valid parameter name on the parameter #{i}, the ParameterName property has the following value: '{commandParameter.ParameterName}'.");

                if (columns.Contains(commandParameter.ParameterName))
                    commandParameter.Value = dataRow[commandParameter.ParameterName];
                else if (columns.Contains(commandParameter.ParameterName.Substring(1)))
                    commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)];

                i++;
            }
        }

        /// <summary>
        /// This method assigns an array of values to an array of IDataParameters
        /// </summary>
        /// <param name="commandParameters">Array of IDataParameters to be assigned values</param>
        /// <param name="parameterValues">Array of objects holding the values to be assigned</param>
        /// <exception cref="ArgumentException">Thrown if an incorrect number of parameters are passed.</exception>
        protected void AssignParameterValues(IDataParameter[] commandParameters, params object[] parameterValues)
        {
            if (commandParameters == null || parameterValues == null)
            {
                // Do nothing if we get no data
                return;
            }

            // We must have the same number of values as we pave parameters to put them in
            if (commandParameters.Length != parameterValues.Length)
            {
                throw new ArgumentException("Parameter count does not match Parameter Value count.");
            }

            // Iterate through the IDataParameters, assigning the values from the corresponding position in the 
            // value array
            for (int i = 0, j = commandParameters.Length, k = 0; i < j; i++)
            {
                if (commandParameters[i].Direction != ParameterDirection.ReturnValue)
                {
                    // If the current array value derives from IDataParameter, then assign its Value property
                    if (parameterValues[k] is IDataParameter value)
                    {
                        IDataParameter paramInstance = value;
                        if (paramInstance.Direction == ParameterDirection.ReturnValue)
                        {
                            paramInstance = (IDataParameter)parameterValues[++k];
                        }
                        commandParameters[i].Value = paramInstance.Value ?? DBNull.Value;
                    }
                    else if (parameterValues[k] == null)
                    {
                        commandParameters[i].Value = DBNull.Value;
                    }
                    else
                    {
                        commandParameters[i].Value = parameterValues[k];
                    }
                    k++;
                }
            }
        }

        /// <summary>
        /// 数据库文件克隆
        /// </summary>
        /// <param name="sourceFilename"></param>
        /// <param name="newName"></param>
        public static void DatabaseClone(string sourceFilename, string newName)
        {
            var fileName = Path.GetFileName(sourceFilename);
            if (fileName != null && newName.ToUpper() == fileName.ToUpper())
            {
                throw new Exception("newName should not equals sourceFilename");
            }
            try
            {
                File.Copy(sourceFilename, newName);
            }
            catch
            {
                throw new IOException("sourceFilename is in using");
            }
        }
    }
}
